package com.joysuch.wwyt.common.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.joysuch.wwyt.bp.entity.BpDepart;
import com.joysuch.wwyt.bp.repository.BpDepartDao;
import com.joysuch.wwyt.common.entity.ExcelTable;
import com.joysuch.wwyt.common.mapper.ExcelTableMapper;
import com.joysuch.wwyt.common.service.ExcelTableService;
import com.joysuch.wwyt.core.bean.SimpleSelectListBean;
import com.joysuch.wwyt.core.common.ajax.ResultBean;
import com.joysuch.wwyt.core.support.Context;
import com.joysuch.wwyt.risk.service.RiskEvaluationExcelImportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import java.util.*;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author zqw
 * @since 2021-05-31
 */
@Service
@Slf4j
public class ExcelTableServiceImpl extends ServiceImpl<ExcelTableMapper, ExcelTable> implements ExcelTableService {

    @Autowired
    private ExcelTableMapper tableMapper;
    //    @Autowired
//    private ExcelFieldMapper fieldMapper;
    @Autowired
    private RiskEvaluationExcelImportService excelService;
    @Autowired
    private BpDepartDao bpDepartDao;

    //    /**
//     * 保存excel表名配置
//     *
//     * @param bean
//     * @return
//     */
//    @Override
//    public ResultBean saveTable(ExcelTableBean bean) {
//        ExcelTable table;
//        LambdaQueryWrapper<ExcelTable> lqw = this.getTableCommonCondition();
////        LambdaQueryWrapper<ExcelTable> lqw = new LambdaQueryWrapper<>();
//        lqw.eq(ExcelTable::getTableName, bean.getTableName());
//        if (bean.getId() != null) {
//            table = tableMapper.selectById(bean.getId());
//        } else {
//            table = new ExcelTable();
//        }
//        BeanUtils.copyProperties(bean, table);
//        if (bean.getId() != null) {
//            lqw.ne(ExcelTable::getId, bean.getId());
//            List<ExcelTable> list = tableMapper.selectList(lqw);
//            if (list.size() > 0) {
//                return ResultBean.wrapErrorResultDefault("表名已存在");
//            }
//            tableMapper.updateById(table);
//        } else {
//            List<ExcelTable> list = tableMapper.selectList(lqw);
//            if (list.size() > 0) {
//                return ResultBean.wrapErrorResultDefault("表名已存在");
//            }
//            tableMapper.insert(table);
//        }
//
//        return ResultBean.success(table);
//    }
//
//    /**
//     * 分页excel表名配置
//     *
//     * @param searchBean
//     * @return
//     */
//    @Override
//    public ResultBean pageTable(ExcelTableSearchBean searchBean) {
//        List<ExcelTableBean> beans = new ArrayList<>();
//        LambdaQueryWrapper<ExcelTable> q = this.getTableCommonCondition();
//        if (StringUtils.isNotBlank(searchBean.getTableName())) {
//            q.like(ExcelTable::getTableName, searchBean.getTableName());
//        }
//        if (StringUtils.isNotBlank(searchBean.getRemark())) {
//            q.like(ExcelTable::getRemark, searchBean.getRemark());
//        }
//        IPage<ExcelTable> page = tableMapper.selectPage(new Page<>(searchBean.getPage() + 1, searchBean.getSize()), q);
//        Integer count = tableMapper.selectCount(q);
//        if (!CollectionUtils.isEmpty(page.getRecords())) {
//            for (ExcelTable table : page.getRecords()) {
//                ExcelTableBean bean = new ExcelTableBean();
//                BeanUtils.copyProperties(table, bean);
//                beans.add(bean);
//            }
//            return ResultBean.pageData(beans, count);
//        }
//        return ResultBean.success(beans);
//    }
//
//
//    /**
//     * 删除excel表名配置
//     *
//     * @param id
//     * @return
//     */
//    @Override
//    public ResultBean deleteTable(Long id) {
//        LambdaQueryWrapper<ExcelField> lqw = this.getFieldCommonCondition();
//        lqw.eq(ExcelField::getTableId, id);
//        List<ExcelField> list = fieldMapper.selectList(lqw);
//        if (!CollectionUtils.isEmpty(list)) {
//            throw new IllegalArgumentException("请先删除该表下的字段配置");
//        }
//        tableMapper.deleteById(id);
//        return ResultBean.success("");
//    }
//
//    /**
//     * 保存excel字段配置
//     *
//     * @param bean
//     * @return
//     */
//    @Override
//    public ResultBean saveField(ExcelFieldBean bean) {
//        ExcelField field;
//        LambdaQueryWrapper<ExcelField> lqw = this.getFieldCommonCondition();
////        LambdaQueryWrapper<ExcelField> lqw = new LambdaQueryWrapper<>();
//        lqw.eq(ExcelField::getFieldName, bean.getFieldName());
//        if (bean.getId() != null) {
//            field = fieldMapper.selectById(bean.getId());
//        } else {
//            field = new ExcelField();
//        }
//        BeanUtils.copyProperties(bean, field);
//        if (bean.getId() != null) {
//            lqw.ne(ExcelField::getId, bean.getId());
//            List<ExcelField> list = fieldMapper.selectList(lqw);
//            if (list.size() > 0) {
//                return ResultBean.wrapErrorResultDefault("字段名已存在");
//            }
//            fieldMapper.updateById(field);
//        } else {
//            List<ExcelField> list = fieldMapper.selectList(lqw);
//            if (list.size() > 0) {
//                return ResultBean.wrapErrorResultDefault("字段名已存在");
//            }
//            fieldMapper.insert(field);
//        }
//
//        return ResultBean.success(field);
//    }
//
//    /**
//     * excel字段配置列表展示
//     *
//     * @param tableId
//     * @return
//     */
//    @Override
//    public ResultBean listField(Long tableId) {
//        LambdaQueryWrapper<ExcelField> lqw = this.getFieldCommonCondition();
//        lqw.eq(ExcelField::getTableId, tableId);
//        List<ExcelField> list = fieldMapper.selectList(lqw);
//        List<ExcelFieldBean> beanList = new ArrayList<>();
//        if (!CollectionUtils.isEmpty(beanList)) {
//            for (ExcelField field : list) {
//                ExcelFieldBean bean = new ExcelFieldBean();
//                BeanUtils.copyProperties(field, bean);
//                beanList.add(bean);
//            }
//        }
//        return ResultBean.success(beanList);
//    }
//
//    /**
//     * 删除excel字段配置
//     *
//     * @param id
//     * @return
//     */
//    @Override
//    public ResultBean deleteField(Long id) {
//        fieldMapper.deleteById(id);
//        return ResultBean.success("");
//    }
//
    private LambdaQueryWrapper<ExcelTable> getTableCommonCondition() {
        LambdaQueryWrapper<ExcelTable> q = new LambdaQueryWrapper<>();
        q.eq(ExcelTable::getTenentId, Context.getCurrentTenantId());
        q.eq(ExcelTable::getDeleteFlag, "0");
        q.likeRight(ExcelTable::getOrgCode, Context.getCompanyCode());
        return q;
    }
//
//    private LambdaQueryWrapper<ExcelField> getFieldCommonCondition() {
//        LambdaQueryWrapper<ExcelField> q = new LambdaQueryWrapper<>();
//        q.eq(ExcelField::getTenentId, Context.getCurrentTenantId());
//        q.eq(ExcelField::getDeleteFlag, "0");
//        q.likeRight(ExcelField::getOrgCode, Context.getCompanyCode());
//        return q;
//    }


    /**
     * excel表名配置列表展示
     *
     * @return
     */
    @Override
    public ResultBean listTable() {
        LambdaQueryWrapper<ExcelTable> condition = this.getTableCommonCondition();
        List<ExcelTable> excelTables = tableMapper.selectList(condition);
        List<SimpleSelectListBean> beanList = new ArrayList<>();
        if (!CollectionUtils.isEmpty(excelTables)) {
            for (ExcelTable table : excelTables) {
                SimpleSelectListBean bean = new SimpleSelectListBean();
                bean.setValue(table.getId());
                bean.setLabel(table.getRemark());
                beanList.add(bean);
            }
        }

        return ResultBean.success(beanList);
    }


    /**
     * 导出
     *
     * @param idList
     * @return
     */
    @Override
    public SXSSFWorkbook exportExcelData(List<Long> idList) {
        //创建一个工作铺
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        LambdaQueryWrapper<ExcelTable> condition = this.getTableCommonCondition();
        condition.in(ExcelTable::getId, idList);
        //获取需要导出的excelTable表配置
        List<ExcelTable> excelTables = tableMapper.selectList(condition);
        //导出部门数据
        this.createDepartSheet(workbook);
        if (!CollectionUtils.isEmpty(excelTables)) {
            for (ExcelTable table : excelTables) {
                if (StringUtils.isNotBlank(table.getTableName()) && StringUtils.isNotBlank(table.getSelectField())) {
                    List<LinkedHashMap<String, String>> dataList = tableMapper.queryTableData(table.getSelectField(), table.getTableName(), table.getShowField(), table.getUnshowValue(), Context.getCompanyCode(), Context.getCurrentTenantId());
                    if (!CollectionUtils.isEmpty(dataList)) {
                        //根据表名创建sheet
                        this.createSheet(workbook, table, dataList);
                    }
                }
            }
        }
        return workbook;
    }

    private void createDepartSheet(SXSSFWorkbook workbook) {
        SXSSFSheet sheet = workbook.createSheet("部门数据");
        SXSSFRow row = null;
        SXSSFCell cell = null;
        //标题
        row = sheet.createRow(0);
        row.setHeightInPoints(20);
        cell = row.createCell(0);
        cell.setCellValue("部门名称");
        cell = row.createCell(1);
        cell.setCellValue("部门Id");
        cell = row.createCell(2);
        cell.setCellValue("部门编码");
        cell = row.createCell(3);
        cell.setCellValue("上级部门Id");
        //查询部门数据
        List<BpDepart> all = bpDepartDao.findAll();
        if (!CollectionUtils.isEmpty(all)) {
            for (int i = 0; i < all.size(); i++) {
                BpDepart depart = all.get(i);
                row = sheet.createRow(i + 1);
                cell = row.createCell(0);
                cell.setCellValue(depart.getName());
                cell = row.createCell(1);
                cell.setCellValue(depart.getId());
                cell = row.createCell(2);
                cell.setCellValue(depart.getCode());
                if (depart.getParentId() != null) {
                    cell = row.createCell(3);
                    cell.setCellValue(depart.getParentId());
                }
            }
        }
    }

    /**
     * 导入
     *
     * @param file
     * @return
     */
    @Override
    public List<String> importExcelData(MultipartFile file) throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        List<String> meaages = new ArrayList<>();
        List<ExcelTable> excelTables = tableMapper.selectList(this.getTableCommonCondition());
        if (workbook != null && !CollectionUtils.isEmpty(excelTables)) {
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                for (ExcelTable table : excelTables) {
                    if (table.getRemark().equals(workbook.getSheetAt(i).getSheetName())) {
                        meaages = this.getSheetData(workbook.getSheetAt(i), meaages, table);
                    }
                }
            }
        }
        return meaages;
    }

    private List<String> getSheetData(XSSFSheet sheet, List<String> meaages, ExcelTable table) throws Exception {
        XSSFRow row;
        //第一行确定id所在列
        row = sheet.getRow(0);
        Integer idIndex = null;
        Integer departIdIndex = null;
        for (int j = 0; j < row.getLastCellNum(); j++) {
            if (row.getCell(j) != null && row.getCell(j).toString().equalsIgnoreCase("id")) {
                idIndex = j;
            }
            if (row.getCell(j) != null && row.getCell(j).toString().equalsIgnoreCase("所属部门Id")) {
                departIdIndex = j;
            }
        }
        Long departId = null;
        Long dataId = null;
        int successcount = 0;
        for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
            row = sheet.getRow(i);
            try {
                if (idIndex != null && row.getCell(idIndex) != null && excelService.getCellValue(row.getCell(idIndex)) != null) {
                    //id
                    dataId = this.getIdFromStr(row, idIndex);
                    if (departIdIndex != null && row.getCell(departIdIndex) != null && excelService.getCellValue(row.getCell(departIdIndex)) != null) {
                        //departId
                        departId = this.getIdFromStr(row, departIdIndex);
                        if (dataId != null && StringUtils.isNotBlank(table.getTableName()) && StringUtils.isNotBlank(table.getUpdateField()) && departId != null) {
                            tableMapper.updateTableData(table.getTableName(), table.getUpdateField(), departId, dataId);
                            successcount++;
                        }
                    }
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                continue;
            }
        }
        meaages.add(sheet.getSheetName() + "导入成功:" + successcount + "条");
        return meaages;
    }

    private Long getIdFromStr(XSSFRow row, Integer idIndex) {
        String str = excelService.getCellValue(row.getCell(idIndex)).toString();
        if (excelService.isNum(str)) {
            return Long.valueOf(excelService.getNumberString(str));
        } else {
            return Long.valueOf(str);
        }
    }

    //创建sheet
    private void createSheet(SXSSFWorkbook workbook, ExcelTable table, List<LinkedHashMap<String, String>> dataList) {
        SXSSFSheet sheet = workbook.createSheet(table.getRemark());
        SXSSFRow row = null;
        SXSSFCell cell = null;
        //标题
        row = sheet.createRow(0);
        row.setHeightInPoints(20);
        int titleIndex = 0;
        Iterator<Map.Entry<String, String>> iterator = dataList.get(0).entrySet().iterator();
        while (iterator.hasNext()) {
            Map.Entry<String, String> next = iterator.next();
            cell = row.createCell(titleIndex);
            cell.setCellValue(next.getKey());
            titleIndex++;
        }
        cell = row.createCell(titleIndex);
        cell.setCellValue("所属部门");
        cell = row.createCell(titleIndex + 1);
        cell.setCellValue("所属部门Id");
        //数据录入
        for (int i = 0; i < dataList.size(); i++) {
            LinkedHashMap<String, String> dataMap = dataList.get(i);
            //行
            row = sheet.createRow(i + 1);
            int cellIndex = 0;
            if (dataMap != null) {
                //每个单元格数据内容
                Iterator<Map.Entry<String, String>> iter = dataMap.entrySet().iterator();
                while (iter.hasNext()) {
                    cell = row.createCell(cellIndex);
                    cell.setCellValue(String.valueOf(iter.next().getValue()));
                    cellIndex++;
                }
            }
        }
    }

}
